using System;
using System.Data;
using System.Data.OleDb;

//using Excel;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Windows.Forms;

namespace ChuanPlus.Utils
{
	/// <summary>
	/// Summary description for clsExcel.
	/// </summary>
	public class clsExcel
	{
		
		private	 string			  m_strSheetName;
		private	 string			  m_strSheetRange;
		private	 bool			  m_blnKeepConnectionOpen=false;
		private  bool			  m_blnMixedData=true;
		private  bool			  m_blnHeaders=true;
		private  string			  m_strExcelFilename;
		private  OleDbConnection  m_oleConn; 
		private  OleDbCommand	  m_oleCmdSelect;
		private static object	  m_objMissing		  =  Missing.Value;

		Microsoft.Office.Interop.Excel.ApplicationClass excelApp;

		#region "Hang so"
		
		private string NAME_EXCELSHEET			= "Level";
		
		#endregion

		public clsExcel()
		{
			
		}

		public string ExcelFilename
		{
			get { return m_strExcelFilename;}
			set { m_strExcelFilename=value;}
		}
		public string SheetName
		{
			get { return m_strSheetName;}
			set { m_strSheetName=value;}
		}
		public string SheetRange
		{
			get {return m_strSheetRange;}
			set 
			{
				if (value.IndexOf(":")==-1) 
					MessageBox.Show("Invalid range length","Message",MessageBoxButtons.OK, MessageBoxIcon.Information); 
				m_strSheetRange=value;}
		}
		public bool Headers
		{
			get { return m_blnHeaders;}
			set { m_blnHeaders=value;}
		}
		public bool MixedData
		{
			get {return m_blnMixedData;}
			set {m_blnMixedData=value;}
		}		
		public bool KeepConnectionOpen
		{
			get { return m_blnKeepConnectionOpen;}
			set {m_blnKeepConnectionOpen=value;}
		}		

		/// <summary>
		/// mo ket noi voi file excel
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa G3
		/// Modified:	18-Oct-2006
		/// </remarks>
		
		public void Open()
		{
			try
			{
				if (m_oleConn !=null)
				{
					if (m_oleConn.State==ConnectionState.Open)
					{
						m_oleConn.Close();
					}
					m_oleConn=null;
				}

				if (System.IO.File.Exists(m_strExcelFilename)==false)
				{
					MessageBox.Show("Excel file " + m_strExcelFilename +  "could not be found.","Message");
				}
				m_oleConn = new OleDbConnection(ExcelConnection());  
				m_oleConn.Open();   				
			}
			catch (Exception ex)
			{
				
			}
		}

		/// <summary>
		/// dong ket noi sau khi select xong
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa G3
		/// Modified:	18-Oct-2006
		/// </remarks>
		
		public void Close()
		{
			if (m_oleConn !=null)
			{
				if (m_oleConn.State != ConnectionState.Closed) 
					m_oleConn.Close(); 
				m_oleConn.Dispose();
				m_oleConn=null;
			}
		}

		/// <summary>
		/// get du lieu tu file excel
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa G3
		/// Modified:	18-Oct-2006
		/// </remarks>
		/// <param name=>strTableName</param>
		/// <returns>DataTable</returns>

		public System.Data.DataTable GetTable(string strTableName)
		
		{
			OleDbDataAdapter oleAdapter = new OleDbDataAdapter();  
			System.Data.DataTable dt = new System.Data.DataTable(strTableName);
			
			try
			{			
				if (m_oleConn ==null) Open();
				if (m_oleConn.State != ConnectionState.Open)
					MessageBox.Show("Connection cannot be opened.","Message",MessageBoxButtons.OK, MessageBoxIcon.Information); 
				if (SetSheetQuerySelect()==false) return null;
				oleAdapter.SelectCommand = m_oleCmdSelect;   
				oleAdapter.FillSchema(dt, SchemaType.Source);  
				oleAdapter.Fill(dt);				
				m_oleCmdSelect.Dispose();
				m_oleCmdSelect=null;
				oleAdapter.Dispose();
				oleAdapter=null;
				if (KeepConnectionOpen==false) Close();					

			}
			catch (Exception ex)
			{
				dt = null;
				
				MessageBox.Show("Excel file error","Message",MessageBoxButtons.OK, MessageBoxIcon.Information);
			}
			return dt;
		}

		/// <summary>
		/// select du lieu trong file excel
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa G3
		/// Modified:	18-Oct-2006
		/// </remarks>
	
		private bool SetSheetQuerySelect()
		{
			try
			{
				if (m_oleConn == null)
				{
					MessageBox.Show("Connection is unassigned or closed.","Message",MessageBoxButtons.OK, MessageBoxIcon.Information); 
				}

				if (m_strSheetName.Length ==0)
					MessageBox.Show("Sheetname was not assigned.","Message",MessageBoxButtons.OK, MessageBoxIcon.Information); 

				m_oleCmdSelect =new OleDbCommand(@"SELECT * FROM [" + m_strSheetName + "$" + m_strSheetRange + "]", m_oleConn);   
			
				return true;
			}			
			catch (Exception ex)
			{
				
			}		
			return false;
		}	

		/// <summary>
		/// tao con string cho excel
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa
		/// Modified:	18-Oct-2006
		/// </remarks>

		private string ExcelConnection()
		{
			return
				@"Provider=Microsoft.Jet.OLEDB.4.0;" + 
				@"Data Source=" + m_strExcelFilename  + ";" + 
				@"Extended Properties=" + Convert.ToChar(34).ToString() + 
				@"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString(); 
		}

		/// <summary>
		/// xet option cho excel con
		/// </summary>
		/// <remarks>
		/// Author:		Nguyen Minh Khoa
		/// Modified:	18-Oct-2006
		/// </remarks>

		private string ExcelConnectionOptions()
		{
			string strOpts="";
			if (this.MixedData ==true)
				strOpts += "Imex=10000;";
			return strOpts;
		}

		public void CreateExcelTemplate()
		{	
			excelApp = new ApplicationClass();
			excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
			Workbook excelBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
			Worksheet excelWorksheet = (Worksheet) excelBook.Worksheets.get_Item(1);		

			excelWorksheet.Name = NAME_EXCELSHEET;			
							
			excelWorksheet.get_Range("A1",m_objMissing).Value2 = "Level ID";
			excelWorksheet.get_Range("A1",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("A1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("A1",m_objMissing).ColumnWidth = 10;

			excelWorksheet.get_Range("B1",m_objMissing).Value2 = "Level Name";
			excelWorksheet.get_Range("B1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("B1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("B1",m_objMissing).ColumnWidth = 30;

			excelWorksheet.get_Range("C1",m_objMissing).Value2 = "Incentive Amount";
			excelWorksheet.get_Range("C1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("C1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("C1",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("D1",m_objMissing).Value2 = "Incentive QTY";
			excelWorksheet.get_Range("D1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("D1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("D1",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("E1",m_objMissing).Value2 = "Max Set";
			excelWorksheet.get_Range("E1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("E1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("E1",m_objMissing).ColumnWidth = 10;			
			
			excelWorksheet.get_Range("F1",m_objMissing).Value2 = "Total Set";
			excelWorksheet.get_Range("F1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("F1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("F1",m_objMissing).ColumnWidth = 10;			

			excelWorksheet.get_Range("G1",m_objMissing).Value2 = "Total Amount";
			excelWorksheet.get_Range("G1",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("G1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("G1",m_objMissing).ColumnWidth = 15;
			
			excelWorksheet.get_Range("H1",m_objMissing).Value2 = "Total Quantity";
			excelWorksheet.get_Range("H1",m_objMissing).Font.Bold = true;	
			excelWorksheet.get_Range("H1",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("H1",m_objMissing).ColumnWidth = 15;

			excelWorksheet.get_Range("A1","F1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);
			excelWorksheet.get_Range("G1","H1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
			
			#region Tao cong thuc va format
			excelWorksheet.get_Range("C2","C2").NumberFormat = "###,###,###,###0";
			excelWorksheet.get_Range("D2","D2").NumberFormat = "###,###,###,###0";
			excelWorksheet.get_Range("E2","E2").NumberFormat = "###,###,###,###0";
			excelWorksheet.get_Range("F2","F2").NumberFormat = "###,###,###,###0";			
			excelWorksheet.get_Range("G2","G2").Formula = "=C2*F2";
			excelWorksheet.get_Range("G2","G2").NumberFormat = "###,###,###,###0";
			excelWorksheet.get_Range("H2","H2").Formula = "=D2*F2";
			excelWorksheet.get_Range("H2","H2").NumberFormat = "###,###,###,###0";
			excelWorksheet.get_Range("C2","H2").AutoFill(excelWorksheet.get_Range("C2","H10"),XlAutoFillType.xlFillDefault);
			#endregion 
					
			excelApp.Visible = true;
		}

		public void CreateAgentTemplate()
		{	
			excelApp = new ApplicationClass();
			excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
			Workbook excelBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
			Worksheet excelWorksheet = (Worksheet) excelBook.Worksheets.get_Item(1);		

			excelWorksheet.Name = "Agent";			
							
			excelWorksheet.get_Range("A2",m_objMissing).Value2 = "Region";
			excelWorksheet.get_Range("A2",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("A2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("A2",m_objMissing).ColumnWidth = 10;

			excelWorksheet.get_Range("B2",m_objMissing).Value2 = "GT";
			excelWorksheet.get_Range("B2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("B2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("B2",m_objMissing).ColumnWidth = 10;

			excelWorksheet.get_Range("C2",m_objMissing).Value2 = "City";
			excelWorksheet.get_Range("C2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("C2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("C2",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("D2",m_objMissing).Value2 = "ASM";
			excelWorksheet.get_Range("D2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("D2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("D2",m_objMissing).ColumnWidth = 30;

			excelWorksheet.get_Range("E2",m_objMissing).Value2 = "Agent Code";
			excelWorksheet.get_Range("E2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("E2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("E2",m_objMissing).ColumnWidth = 15;			
			
			excelWorksheet.get_Range("F2",m_objMissing).Value2 = "Agent Name";
			excelWorksheet.get_Range("F2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("F2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("F2",m_objMissing).ColumnWidth = 30;			

			excelWorksheet.get_Range("G2",m_objMissing).Value2 = "Address";
			excelWorksheet.get_Range("G2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("G2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("G2",m_objMissing).ColumnWidth = 40;

			excelWorksheet.get_Range("H2",m_objMissing).Value2 = "Description";
			excelWorksheet.get_Range("H2",m_objMissing).Font.Bold = true;
			excelWorksheet.get_Range("H2",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("H2",m_objMissing).ColumnWidth = 30;
			
			excelWorksheet.get_Range("A2","H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);					
								
			excelApp.Visible = true;
		}
		/// <summary>
		/// Create Template Excel for Existed Customer
		/// </summary>
		/// <param name="Wb"></param>
		/// <param name="Cancel"></param>
		/// <remarks>
		/// Author:		Huynh Co Tai
		/// Modified:	18-Apr-2008
		/// </remarks>
		public void CreateExistCustomerTemplate(string FileName, System.Data.DataTable dt)
		{	
			excelApp = new ApplicationClass();
			excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
			Workbook excelBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
			Worksheet excelWorksheet = (Worksheet) excelBook.Worksheets.get_Item(1);		

			excelWorksheet.Name = "Registered Customer";			

			excelWorksheet.get_Range("A1","E1").Merge(Type.Missing) ;
			excelWorksheet.get_Range("A1","E1").RowHeight = 40;
			excelWorksheet.get_Range("A1","E1").Font.Size = 20;
			excelWorksheet.get_Range("A1","E1").Font.Bold = true;
			excelWorksheet.get_Range("A1","E1").HorizontalAlignment = Excel.Constants.xlCenter;
			excelWorksheet.get_Range("A1","E1").VerticalAlignment = Excel.Constants.xlCenter;
			//excelWorksheet.get_Range("A1","E1").Justify();
			excelWorksheet.get_Range("A1","E1").Value2 = "Duplicate Customer";

			excelWorksheet.get_Range("A3",m_objMissing).Value2 = "SchemeID";
			excelWorksheet.get_Range("A3",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("A3",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("A3",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("B3",m_objMissing).Value2 = "RegionCode";
			excelWorksheet.get_Range("B3",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("B3",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("B3",m_objMissing).ColumnWidth = 20;
			
			excelWorksheet.get_Range("C3",m_objMissing).Value2 = "StrategicCityCode";
			excelWorksheet.get_Range("C3",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("C3",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("C3",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("D3",m_objMissing).Value2 = "CompanyCode";
			excelWorksheet.get_Range("D3",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("D3",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("D3",m_objMissing).ColumnWidth = 20;

			excelWorksheet.get_Range("E3",m_objMissing).Value2 = "CustID";
			excelWorksheet.get_Range("E3",m_objMissing).Font.Bold = true;				
			excelWorksheet.get_Range("E3",m_objMissing).BorderAround(m_objMissing,XlBorderWeight.xlThin,XlColorIndex.xlColorIndexAutomatic,m_objMissing);
			excelWorksheet.get_Range("E3",m_objMissing).ColumnWidth = 20;

            excelWorksheet.get_Range("A3","E3").RowHeight = 25;			
			excelWorksheet.get_Range("A3","E3").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SkyBlue);					
			//excelWorksheet.get_Range("A1","E1").Value2 = strText;
			int i =4;
			foreach(DataRow dr in dt.Rows)
			{
				excelWorksheet.get_Range("A" + i ,m_objMissing).Value2 = dr[0];
				excelWorksheet.get_Range("B" + i ,m_objMissing).Value2 = dr[1];
				excelWorksheet.get_Range("C" + i ,m_objMissing).Value2 = dr[2];
				excelWorksheet.get_Range("D" + i ,m_objMissing).Value2 = dr[3];
				excelWorksheet.get_Range("E" + i ,m_objMissing).Value2 = dr[4];
				i++;
			}
			excelBook.SaveCopyAs(FileName);		
			excelApp.Visible = true;
		}
		/// <summary>
		/// Exit excel application if workbook is closed
		/// </summary>
		/// <param name="Wb"></param>
		/// <param name="Cancel"></param>
		/// <remarks>
		/// Author:		Nguyen Bao Nguyen G3
		/// Modified:	21-Nov-2006
		/// </remarks>
		private void excelApp_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
		{
			excelApp.Quit();
			System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
			excelApp = null;
			System.GC.Collect();
		}
	}
}
